package com.atguigu.eduservice.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class SQLServerTableCompare {

    // 第一个数据库的连接字符串
    private static final String DB_URL1 = "jdbc:sqlserver://localhost:1433;databaseName=Iqiancheng;user=sa;password=intersky;";

    // 第二个数据库的连接字符串
    private static final String DB_URL2 = "jdbc:sqlserver://122.228.131.186:9000;databaseName=bolianTest;user=zxb;password=zxb7255;";

    // 获取到第一个数据库的连接
    private Connection getConnection1() throws SQLException {
        return DriverManager.getConnection(DB_URL1);
    }

    // 获取到第二个数据库的连接
    private Connection getConnection2() throws SQLException {
        return DriverManager.getConnection(DB_URL2);
    }

    // 查询并返回表的列名列表
    private List<String> getColumns(Connection conn, String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        String sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "'";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                columns.add(rs.getString("COLUMN_NAME"));
            }
        }
        return columns;
    }

    public void compareTables(String tableName1, String tableName2) {
        try (Connection conn1 = getConnection1();
             Connection conn2 = getConnection2()) {

            List<String> columns1 = getColumns(conn1, tableName1);
            List<String> columns2 = getColumns(conn2, tableName2);

            Set<String> commonColumns = new HashSet<>(columns1);
            commonColumns.retainAll(columns2);

            Set<String> uniqueColumns1 = new HashSet<>(columns1);
            uniqueColumns1.removeAll(columns2);

            Set<String> uniqueColumns2 = new HashSet<>(columns2);
            uniqueColumns2.removeAll(columns1);

            System.out.println("Common Columns: " + commonColumns);
            System.out.println("Unique in Table 1: " + uniqueColumns1);
            System.out.println("Unique in Table 2: " + uniqueColumns2);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        SQLServerTableCompare comparer = new SQLServerTableCompare();
        String sourcestr = "买方代码主表,买方代码子表,非LC限额申请主表,非LC限额申请子表,出运申报主表,出运申报子表";
        String deststr = "BuyerCodeApply,BuyerCodeReply,NoLcQuotaApply,NoLcQuotaReply,ShipmentApply,ShipmentApproveInfo";
        String[] sourcearr = sourcestr.split(",");
        String[] destarr = deststr.split(",");

        for (int i=0;i<sourcearr.length;i++) {
            System.out.println(sourcearr[i] + "-" + destarr[i] + "比较");

            comparer.compareTables(destarr[i], destarr[i]); // 确保这里的表名与你的数据库中的表名相匹配
        }
        }
}